﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using Oracle.DataAccess.Client;
using System.IO;
using System.Text;

namespace Wicresoft.UnifyShow.Business
{
    public static class ToDoHelper
    {

        private static readonly string itsmCM = @"(SELECT  Incident_Number AS IDS,'事件' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Submit_Date+28800,'second')
as CreateDate,case ToDo.cchr_submitterfullname when '自服务门户' THEN '自服务系统' when '监控系统' THEN '监控系统' ELSE cp.full_name END as  Submitter,ToDo.Description,'指派' as Operation,  
CASE ToDo.Status  WHEN 0 THEN '新建'  WHEN 1 THEN '已指派' WHEN 2 THEN '进行中' WHEN 3 THEN '暂停' ELSE '其他' END  AS Status,
CASE ToDo.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN 3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.Assignee AS Assigner, ToDo.Assigned_Group AS AssignGroup FROM aradmin.hpd_help_desk ToDo
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE  ((( ToDo.Assignee is null AND EXISTS(SELECT csga.support_group_id FROM  aradmin.ctm_support_group_association csga WHERE 
ToDo.Assigned_Group_ID=csga.support_group_id AND csga.login_id=:loginID))) OR ToDo.assignee_login_id=:loginID)
 AND ToDo.status<4)
Union ALL 
(SELECT  Problem_Investigation_ID AS IDS,'问题' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Submit_Date+28800,'second')
as CreateDate,cp.full_name as  Submitter,ToDo.Description,'指派' as Operation,  
CASE ToDo.Investigation_Status  WHEN 0 THEN '草稿'  WHEN 1 THEN '评审中' WHEN 2 THEN '授权请求' WHEN 3 THEN '已指派' WHEN 4 THEN '调查中' WHEN 5 THEN '暂停' ELSE '其他' END  AS Status,
CASE ToDo.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN 3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.Assignee AS Assigner, ToDo.Assigned_Group AS AssignGroup FROM aradmin.pbm_problem_investigation ToDo 
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE  ((( ToDo.Assignee is null AND EXISTS(SELECT csga.support_group_id FROM  aradmin.ctm_support_group_association csga WHERE 
ToDo.Assigned_Group_ID=csga.support_group_id AND csga.login_id=:loginID))) OR ToDo.Assignee_Login_ID=:loginID OR ToDo.Problem_Manager_Login=:loginID)
AND ToDo.Investigation_Status<6)
Union ALL 
(SELECT ToDo.Task_ID AS IDS,'任务' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Create_Date+28800,'second')as CreateDate,
cp.full_name as  Submitter,ToDo.Summary AS description,'指派' as Operation,  
CASE ToDo.Status WHEN 2000 THEN '已指派' ELSE '其他' END  AS Status,
CASE ToDo.Priority WHEN 0 THEN '严重'  WHEN 1 THEN '高' WHEN 2 THEN '中等' WHEN 3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.Assignee AS Assigner, ToDo.Assignee_Group AS AssignGroup FROM aradmin.tms_task ToDo 
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE  ToDo.Assigned_To=:loginID AND ToDo.Status=2000)
Union ALL
(SELECT ToDo.Request_ID AS IDS,'变更' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Submit_Date+28800,'second')
as CreateDate,cp.full_name as  Submitter,ToDo.Description,'审批'as Operation,
CASE  ToDo.Change_Request_Status  WHEN 1 THEN '授权请求'  WHEN 2 THEN '变更请求'  ELSE '其他' END  AS Status,
CASE ToDo.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN  3 THEN '低' ELSE '其他' END  AS Priority,
'' AS Assigner,'' AS AssignGroup FROM aradmin.ap_detail_signature ads
INNER JOIN aradmin.chg_infrastructure_change ToDo ON ads.request=ToDo.request_id
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE ads.approval_status=0 AND ads.application='CHG:Infrastructure Change' AND ads.approvers LIKE '%' || :loginID || '%' )
Union ALL
(SELECT ToDo.Request_ID AS IDS,'变更' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Submit_Date+28800,'second')
as CreateDate,cp.full_name as  Submitter,ToDo.Description,'指派'as Operation,
CASE  ToDo.Change_Request_Status WHEN 0 THEN '草稿'  WHEN 1 THEN '授权请求'  WHEN 2 THEN '变更请求' WHEN 3 THEN '进行中的计划' WHEN 7 THEN '正在执行' 
WHEN 8 THEN '暂停' ELSE '其他' END  AS Status,
CASE ToDo.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN  3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.ASCHG AS Assigner,ToDo.ASGRP AS AssignGroup FROM aradmin.chg_infrastructure_change ToDo 
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE ToDo.ASLOGID=:loginID AND ToDo.Change_Request_Status<9)";
        private static readonly string sfCM = @"(SELECT 'SFR0'|| ToDo.ServiceRequestID AS IDS, '自服务' as TypeS,ToDo.CreateTime as CreateDate,
d.UserName AS Submitter,ToDo.description AS Description,'审批' as Operation,  
CASE ToDo.Stauts  WHEN 0 THEN '草稿'  WHEN 1 THEN '审批中' WHEN 2 THEN '等待中' WHEN 3 THEN '处理中' ELSE '其他' END  AS Status,
'' as Priority, '' AS Assigner,
CASE  ToDo.step WHEN 1 THEN '地市接口人' WHEN 2 THEN '地市主任' WHEN 3 THEN '省接口人'  WHEN 4 THEN '省主任'  ELSE '其他' END  
AS AssignGroup FROM ADMIN.servicerecords ToDo
INNER JOIN admin.accountcopy a  ON ToDo.companyids like  ('%'||a.company||'%')
INNER JOIN admin.userrolerelationship b ON a.accountid=b.accountid
INNER JOIN admin.approvalrole c ON  b.roleid=c.ID and c.id=ToDo.step
INNER JOIN admin.accountcopy d  ON ToDo.accountid= d.accountid
WHERE ToDo.Stauts=1 and a.accountid=:loginID )";

        public static List<ToDoModel> GetListToDo(string loginID,out int Count, int? pageIndex = null, int? pageSize = null)
        {
            string connectionStrSF = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
            string connectionStrITSM = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
            List<ToDoModel> result1 = new List<ToDoModel>();
            using (OracleConnection conSF = new OracleConnection(connectionStrSF), conITSM = new OracleConnection(connectionStrITSM))
            {
                conSF.Open();
                conITSM.Open();
                OracleCommand commandSF, commandITSM;
                commandSF = conSF.CreateCommand();
                commandITSM = conITSM.CreateCommand();
                commandSF.CommandType = System.Data.CommandType.Text;
                commandITSM.CommandType = System.Data.CommandType.Text;
                commandSF.BindByName = true;
                commandITSM.BindByName = true;
                commandSF.CommandText = sfCM;
                commandITSM.CommandText = itsmCM;
                OracleParameter loginIDSF = new OracleParameter(":loginID", OracleDbType.NVarchar2, 100, loginID, System.Data.ParameterDirection.Input);
                OracleParameter loginIDITSM = new OracleParameter(":loginID", OracleDbType.NVarchar2, 100, ConvertString(loginID), System.Data.ParameterDirection.Input);
                commandSF.Parameters.Add(loginIDSF);
                commandITSM.Parameters.Add(loginIDITSM);
                var result = GetListToDoFromCommand(commandSF).Concat(GetListToDoFromCommand(commandITSM)).OrderBy(o => o.IDS);

                Count = result.Count();

                if (pageIndex.HasValue)
                {
                    result1 = result.Skip(pageSize.Value * (pageIndex.Value - 1)).Take(pageSize.Value).ToList();
                }
                else
                {
                    result1 = result.ToList();
                }
            }
            return result1;
        }

        private static string ConvertString(string str)
        {
            if (str == null) return "";
            Encoding enc = Encoding.UTF8;
            return enc.GetString(System.Text.Encoding.UTF8.GetBytes(str));
        }


        private static List<ToDoModel> GetListToDoFromCommand(OracleCommand com)
        {
            List<ToDoModel> items = new List<ToDoModel>();
            ToDoModel item = null;
            string description=string.Empty;
            using (var reader = com.ExecuteReader())
            {
                while (reader.Read())
                {
                    item = new ToDoModel();
                    item.IDS = (string)reader["IDS"];
                    item.TypeS = (string)reader["TypeS"];
                    item.Submitter = (string)reader["Submitter"];

                    description=reader["Description"] == DBNull.Value ? "" : ((string)reader["Description"]);
                    if(description.Length>15) description=description.Substring(0,15);

                    item.Description = description;
                    item.Operation = (string)reader["Operation"];
                    item.Status = (string)reader["Status"];
                    item.Priority = reader["Priority"] == DBNull.Value ? "" : (string)reader["Priority"];
                    item.Assigner = reader["Assigner"] == DBNull.Value ? "" : (string)reader["Assigner"];
                    item.AssignGroup = reader["AssignGroup"] == DBNull.Value ? "" : (string)reader["AssignGroup"];
                    item.CreateDate = (DateTime)reader["CreateDate"];
                    items.Add(item);
                }
            }
            com.Dispose();
            return items;
        }
    }
}